package SSP;
import java.sql.*;

public class Customer {

private int customerid = 0;
private String customername = null;
private String customertypedesc = null;
private String billingstreet = null;
private String billingcity = null;
private String billingstate = null;
private String billingzip = null;
private String firstname = null;
private String lastname = null;
private String phone = null;
private String email = null;
private String position =null;
private ResultSet rs = null;
private CallableStatement csmt = null;
private PreparedStatement ps = null;
private OraDB myDB = new OraDB();
private int updateResult;

public synchronized int CustomerUpdate()
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
{
 CallableStatement csmt = null;
 String mySQL = "{call CustomerUpdateSmith(?,?,?,?,?,?,?,?,?,?,?,?)}";
 myDB.createConnection();
 csmt = myDB.conn.prepareCall(mySQL);
 csmt.setInt(1, customerid);
 csmt.setString(2, customername);
 csmt.setString(3, billingstreet);
 csmt.setString(4, billingcity);
 csmt.setString(5, billingstate);
 csmt.setString(6, billingzip);
 csmt.setString(7, firstname);
 csmt.setString(8, lastname);
 csmt.setString(9, phone);
 csmt.setString(10, email);
 csmt.setString(11, position);
 csmt.registerOutParameter(12,Types.INTEGER);
 
 csmt.executeQuery();
 updateResult = csmt.getInt(12);

 myDB.closeConnection();
 return updateResult;
}


public ResultSet getCustomerList()
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

myDB.createConnection();
String myQuery = "SELECT c.customername, c.customerid, ct.customertypedesc," +
" c.billingcity, c.billingstate, c.contactfirstname, c.contactlastname, c.contactphone" +
" from customer c, customertype ct" +
" where c.customertypeid = ct.customertypeid" +
" order by c.customername asc";

ps = myDB.conn.prepareStatement(myQuery);
rs = ps.executeQuery();
return rs;
}


public void getCustomerDetails()
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

myDB.createConnection();
String myQuery = "Select c.customerid, c.customername, c.billingstreet, c.billingcity, " +
		"c.billingstate, c.billingzip, c.contactfirstname, c.contactlastname, " +
        "c.contactphone, c.contactemail, c.contactposition, ct.customertypedesc " +
        "from customer c, customertype ct " +
        "where c.customertypeid = ct.customertypeid and c.customerid = ?";

ps = myDB.conn.prepareStatement(myQuery);
ps.setInt(1,customerid);
rs = ps.executeQuery();

while (rs.next()) {
customerid = rs.getInt("customerid");
customername = rs.getString("customername");
customertypedesc = rs.getString("customertypedesc");
billingstreet = rs.getString("billingstreet");
billingcity = rs.getString("billingcity");
billingstate = rs.getString("billingstate");
billingzip = rs.getString("billingzip");
firstname = rs.getString("contactfirstname");
lastname = rs.getString("contactlastname");
phone = rs.getString("contactphone");
email = rs.getString("contactemail");
position = rs.getString("contactposition");
}
closeConnection();
}

public ResultSet getOpenRequests(int customerid)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

myDB.createConnection();
String myQuery = "select so.serviceorderid, e.equipmentid, p.productname, " +
		"st.servicetypedescription, sos.soStatusDescription " +
        "from serviceorder so, equipment e, product p, servicetype st, serviceorderstatus sos, location l " +
        "where so.servicetypeid = st.servicetypeid and " +
        "so.equipmentid = e.equipmentid and " +
        "so.sostatusid = sos.sostatusid and " +
        "e.productid = p.productid and " +
        "e.locationid = l.locationid and  " +
        "l.customerid = ? and sos.soStatusID <> 1 " +
        "order by 1";

ps = myDB.conn.prepareStatement(myQuery);
ps.setInt(1,customerid);
rs = ps.executeQuery();
return rs;
}

public ResultSet getOverDuePM(int customerid)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

myDB.createConnection();
// sysdate = 2/1/2009

String myQuery ="select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') " +
				" as LastService, to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  " +
				" round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))) as DaysOverDue  " +
				" from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm where so.equipmentid = e.equipmentid  " +
				" and e.productid = p.productid and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and  " +
				" e.statusid <>2 and so.equipmentid in  " +
				"	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and l.customerid =?) group by e.equipmentid, p.productname,  " +
				"	   pm.pmdescription, pm.pmid, ppm.frequency having  " +
				"	      (round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) > 0 and  " +
				"	      ((max(so.closeddate)) > (max(so.opendate))) order by 7 desc, 1";

ps = myDB.conn.prepareStatement(myQuery);
ps.setInt(1,customerid);
rs = ps.executeQuery();
return rs;
}

public ResultSet getDueSoon(int customerid)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

myDB.createConnection();
//sysdate = 2/1/2009

String myQuery = "select e.equipmentid, p.productname, pm.pmdescription, ppm.frequency, to_char(max(so.closeddate), 'MM/DD/YYYY') as LastService, " +
				" to_char((max(so.closeddate) + ppm.frequency), 'MM/DD/YYYY') as ServiceDue,  " +
				" round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) as DaysUntilDue  " +
				" from serviceorder so, equipment e, product p, productpm ppm, preventivemaintenance pm  " +
				" where so.equipmentid = e.equipmentid and e.productid = p.productid  " +
				" and p.productid = ppm.productid and ppm.pmid = pm.pmid and so.pmid = pm.pmid and e.statusid <>2 and  " +
				" so.equipmentid in  " +
				"	(select e.equipmentid from equipment e, location l where e.locationid = l.locationid and  " +
				"      	l.customerid =?) group by e.equipmentid, p.productname, pm.pmdescription, pm.pmid, ppm.frequency having  " +
				" 	(round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency)))) < 0 and  " +
				" 	round(((to_date('02/01/2009', 'MM/DD/YYYY')) - (max(so.closeddate) + ppm.frequency))*-1) < 30  " +
				"	and ((max(so.closeddate)) > (max(so.opendate))) order by 7, 1";

ps = myDB.conn.prepareStatement(myQuery);
ps.setInt(1,customerid);
rs = ps.executeQuery();
return rs;
}

public int getCustomerid() {
	return customerid;
}
public void setCustomerid(int userid) {
	this.customerid = userid;

}
public String getCustomerTypeDesc() {
	if (customertypedesc==null) customertypedesc="";
	return customertypedesc;
}
public void setCustomerTypeDesc(String customertypedesc) {
	this.customertypedesc = customertypedesc;
}
public String getCustomername() {
	if (customername==null) customername="";
	return customername;
}
public void setCustomername(String customername) {
	this.customername = customername;
}
public String getBillingstreet() {
	if (billingstreet==null) billingstreet="";
	return billingstreet;
}
public void setBillingstreet(String billingstreet) {
	this.billingstreet = billingstreet;
}
public String getBillingcity() {
	if (billingcity==null) billingcity="";
	return billingcity;
}
public void setBillingcity(String billingcity) {
	this.billingcity = billingcity;
}
public String getBillingstate() {
	if (billingstate==null) billingstate="";
	return billingstate;
}
public void setBillingstate(String billingstate) {
	this.billingstate = billingstate;
}
public String getBillingzip() {
	if (billingzip==null) billingzip="";
	return billingzip;
}
public void setBillingzip(String billingzip) {
	this.billingzip = billingzip;
}
public String getFirstname() {
	if (firstname==null) firstname="";
	return firstname;
}
public void setFirstname(String firstname) {
	this.firstname = firstname;
}
public String getLastname() {
	if (lastname==null) lastname="";
	return lastname;
}
public void setLastname(String lastname) {
	this.lastname = lastname;
}
public String getPhone() {
	if (phone==null) phone="";
	return phone;
}
public void setPhone(String phone) {
	this.phone = phone;
}
public String getEmail() {
	if (email==null) email="";
	return email;
}
public void setEmail(String email) {
	this.email = email;
}
public String getPosition() {
	if (position==null) position="";
	return position;
}
public void setPosition(String position) {
	this.position = position;
}
public void closeConnection()
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
	 myDB.closeConnection();
}

}

